﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ZhuGe.DAL;
using ZhuGe.DAL.SQLite;
using ZhuGe.DAL.SqlServer;
using ZhuGe.Config;

namespace ZhuGe.DataManage
{
    public partial class frmTables : Form
    {
        private INIFile ini = new INIFile();
        public frmTables()
        {
            InitializeComponent();
            
            InitDBConfig();
        }

        public void InitDBConfig()
        {
            this.txt_IP.Text = ini.IniReadValue("DB", "IP");

            this.txt_DBName.Text = ini.IniReadValue("DB", "DBName");

            this.txt_UserName.Text = ini.IniReadValue("DB", "UserName");

            this.txt_Pwd.Text = ini.IniReadValue("DB", "Pwd");
        }

        public string GetConStr()
        {
            return String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};pwd={3}"
                                , ini.IniReadValue("DB", "IP"), ini.IniReadValue("DB", "DBName"), ini.IniReadValue("DB", "UserName"), ini.IniReadValue("DB", "Pwd"));
        }

        #region  数据库相关配置

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnTestDBConnect_Click(object sender, EventArgs e)
        {
            bool res =false;//= MySqlHelper.TestConnect(this.txt_IP.Text.Trim(), this.txt_UserName.Text.Trim(), this.txt_Pwd.Text.Trim(), this.txt_DBName.Text.Trim());

            string con = GetConStr();

            if (string.IsNullOrEmpty(con))
            {
                MessageBox.Show("数据库连接失败！");
                return;
            }

            res = SqlHelper.TestConnect(con);

            if (res)
            {
                MessageBox.Show("数据库连接成功！");
            }
            else
            {
                MessageBox.Show("数据库连接失败！");
            }
        }

        #endregion
        
        /// <summary>
        /// 查询所有表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_SelectTables_Click_1(object sender, EventArgs e)
        {
            SqlHelper.SetConnect(GetConStr());
            this.dgv_ShowTables.DataSource = SqlHelper.ExecuteDataSet("select * from sysobjects where xtype='U';", "tables", null).Tables[0];
        }

        /// <summary>
        /// 查询表字段详细数据
        /// </summary>
        /// <param name="tableName"></param>
        public void SelectColumns(string tableName)
        {
            //is_identity 自动增长列
            //case when a.is_nullable = 0 then 'Not Null' else 'Null' end as nullable,
            string cmdSql = @" SELECT t.[name] AS [表名],c.[name] AS [字段名],cast(ep.[value]
                                  as varchar(100)) AS [字段说明],c.max_length as [字段名长度],
                                  (select top 1 name from systypes where xtype=c.system_type_id) as 字段类型
                                  FROM sys.tables AS t 
                                  INNER JOIN sys.columns AS c ON t.object_id= c.object_id 
                                  LEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id 
                                  WHERE ep.class =1  and t.name='{0}'";

            this.dgv_Columns.DataSource = SqlHelper.ExecuteDataSet(String.Format(cmdSql,tableName), "columns", null).Tables[0];

            this.tsLable_CurTable.Text = String.Format("当前表名称：{0}", tableName);
        }

        /// <summary>
        /// 双击查看表字段详细数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgv_ShowTables_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            SqlHelper.SetConnect(GetConStr());
            string tableName = this.dgv_ShowTables.Rows[e.RowIndex].Cells["name"].Value.ToString();
            SelectColumns(tableName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSaveDBConfig_Click(object sender, EventArgs e)
        {
            try
            {
                ini.IniWriteValue("DB", "IP", this.txt_IP.Text);
                ini.IniWriteValue("DB", "DBName", this.txt_DBName.Text);
                ini.IniWriteValue("DB", "UserName", this.txt_UserName.Text);
                ini.IniWriteValue("DB", "Pwd", this.txt_Pwd.Text);

                MessageBox.Show("保存成功！");
            }
            catch (Exception)
            {
                MessageBox.Show("保存失败！");
                throw;
            }
        }
    }
}
